﻿Imports Excel
Imports System.Windows.Forms

Public Class BudgetD
    Inherits ExcelCreateBase

    Public Event PageComplete(ByVal page As Integer, ByVal totalPage As Integer)
    Public Event PrintProcess(ByVal content As String)
    Private _endColumnName = "E"

    Public Sub New(ByVal fileName As String)
        MyBase.New(fileName)
    End Sub

    Private Sub PageSetUp()
        With Me.GetCurrentWorkSheet.PageSetup
            .LeftMargin = CentimetersToPoints(0.1)
            .RightMargin = CentimetersToPoints(0.1)
            .TopMargin = CentimetersToPoints(0.4)
            .BottomMargin = CentimetersToPoints(0.4)
            .HeaderMargin = CentimetersToPoints(0.4)
            .FooterMargin = CentimetersToPoints(0.4)
            .CenterHorizontally = True
        End With
    End Sub

    Public Sub DoOutData(ByVal dt As System.Data.DataTable)
        '设置当前SHEET
        CurrentSheetIndex = 1

        '打开Excel
        RaiseEvent PrintProcess("正在打开Excel")
        打开()

        '进行页面设置
        RaiseEvent PrintProcess("正在页面设置")
        PageSetUp()

        '计算页数
        RaiseEvent PrintProcess("正在计算打印页数")
        Dim page As Integer = Fix(dt.Rows.Count / (报表记录条数字 - 1))
        If dt.Rows.Count Mod (报表记录条数字 - 1) <> 0 Then
            page += 1
        End If

        RaiseEvent PrintProcess("共" & page & "页")
        '设置每一页的模版
        For index As Integer = 1 To page
            '第一页 先设置好做模版
            If index = 1 Then
                RaiseEvent PrintProcess("正在初始化模版")
                SetTemp()
            Else
                SetTemp(index)
            End If
        Next
        RaiseEvent PrintProcess("初始化模版成功")

        RaiseEvent PrintProcess("正在打印内容")
        PrintContent(dt)

        RaiseEvent PrintProcess("个别设置")
        PrintP(dt)

        RaiseEvent PrintProcess("打印结束")
        保存()

        RaiseEvent PrintProcess("正在关闭......")
        关闭()

        RaiseEvent PrintProcess("")

    End Sub

    Private Sub SetTemp(ByVal page As Integer)
        CurrentSheetIndex = 1
        With GetCurrentWorkSheet.Range("A1:" & _endColumnName & 报表记录条数字)
            .Copy()
        End With

        With Me.GetCurrentWorkSheet
            With .Range("A" & CStr((page - 1) * 报表记录条数字 + 1))
                '复制格式
                .PasteSpecial(XlPasteType.xlPasteFormats, XlPasteSpecialOperation.xlPasteSpecialOperationNone, False, False)
                '复制列宽
                .PasteSpecial(XlPasteType.xlPasteColumnWidths, XlPasteSpecialOperation.xlPasteSpecialOperationNone, False, False)
            End With                    '
        End With

    End Sub

    Private Sub SetTemp()

        '只设置格式 不要设置单元格 内容
        With Me.GetCurrentWorkSheet
            '设置列宽
            .Columns.Item("A:A", Type.Missing).ColumnWidth = 13
            .Columns.Item("B:B", Type.Missing).ColumnWidth = 18

            .Columns.Item("C:C", Type.Missing).ColumnWidth = 18
            .Columns.Item("D:D", Type.Missing).ColumnWidth = 18

            .Columns.Item("E:E", Type.Missing).ColumnWidth = 13

            '设置标题背景边框
            With .Range("A1:" & _endColumnName & "1")
                .Interior.ColorIndex = 15
                With .Font
                    .Bold = True
                    .Name = 报表字体
                    .Size = 12
                    .Strikethrough = False
                    .Superscript = False
                    .Subscript = False
                    .OutlineFont = False
                    .Shadow = False
                    .Underline = -4142
                End With
            End With

            '设置边框
            With .Range("A1:" & _endColumnName & 报表记录条数字)
                With .Font
                    .Name = 报表字体
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeTop)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeRight)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
                With .Borders(Excel.XlBordersIndex.xlInsideVertical)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
                With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
            End With

        End With
    End Sub

    Private Sub PrintP(ByVal dt As System.Data.DataTable)
        'Me.GetCurrentWorkBook.F()
    End Sub

    Private Sub PrintContent(ByVal dt As System.Data.DataTable)

        Dim page As Integer = Fix(dt.Rows.Count / (报表记录条数字 - 1))
        If dt.Rows.Count Mod (报表记录条数字 - 1) <> 0 Then
            page += 1
        End If

        Dim sb As New System.Text.StringBuilder
        For index As Integer = 1 To page
            Dim startRow As Integer = (index - 1) * (报表记录条数字 - 1)

            sb.AppendLine("预算日期" & vbTab & "预算分类" & vbTab & "预算分类" & vbTab & "预算金额" & vbTab & "预算用户")
            For jdex As Integer = 1 To 报表记录条数字 - 1
                startRow += 1
                If startRow > dt.Rows.Count Then
                    Exit For
                End If

                sb.Append(CStr(dt.Rows(startRow - 1)("预算日期")).Trim)
                sb.Append(vbTab)

                sb.Append(CStr(dt.Rows(startRow - 1)("分类1")).Trim)
                sb.Append(vbTab)

                sb.Append(CStr(dt.Rows(startRow - 1)("分类2")).Trim)
                sb.Append(vbTab)

                sb.Append(CStr(dt.Rows(startRow - 1)("金额")).Trim)
                sb.Append(vbTab)

                sb.AppendLine(CStr(dt.Rows(startRow - 1)("用户")).Trim)
            Next
        Next

        Dim con As String = sb.ToString.Trim
        If con.Length > 0 Then
            With Me.GetCurrentWorkSheet
                Clipboard.SetDataObject(con)
                .Range("A1").Select()
                .Paste()
            End With
        End If
    End Sub

#Region "报表必须使用"
    Private _报表字体 As String = "宋体"
    Public Overrides Property 报表字体() As String
        Get
            Return _报表字体
        End Get
        Set(ByVal value As String)
            _报表字体 = value
        End Set
    End Property

    Private _报表记录条数字 As Integer = 55
    Public Overrides Property 报表记录条数字() As Integer
        Get
            Return _报表记录条数字
        End Get
        Set(ByVal value As Integer)
            _报表记录条数字 = value
        End Set
    End Property
#End Region

End Class
